Find And Replace (REPLACE.MDA) Summary: This program provides a "Find and Replace" function for Queries, Forms, Reports, and Macros (MSAccess 1.1 only provides Find and Replace for Modules). I wrote this module to facilitate name changes since MSAccess provides such poor name change support. The intended user of this module is a programmer. To install and run Find and Replace: 1 Move the replace.mda file to your ACCESS directory (probably c:\ACCESS) 2 Using Microsoft Windows Notepad or another text editor, open MSACCESS.INI (in your WINDOWS directory), and add the following lines under the sections indicated ([Libraries] and [Menu Add-ins]; add these section headings if they are not already present): [Libraries] replace.mda=ro [Menu Add-ins] &Find and Replace==StartFindAndReplace() 3 Save the file, and restart Microsoft Access. 4 Open the database you want to use Find and Replace with. 5 Find and Replace now appears as a choice on the help menu. Select it. The Find and Replace form should appear. 6 Enter the text to be searched for and the text that will replace it. WildCards are not permitted. 7 If you want to restrict the search to Match Whole Words only (words consist of A-Z, a-z, _, 0-9) check the "Match Whole Words/Names Only" box. If you want to search only objects with a certain name enter that in the "Object Names Like" box (wildcards OK). If you want Find and Replace to try to capitalize things properly check "Clean-up to Proper Case" (It will look on both sides of the found string, to the beginning and end of the word containing the found string, to perform this; it will not capitalize lower case prefixes as defined by the Leszynski/Reddick naming standard; it will not change upper case to lower case). The proper-case cleanup is usually only useful when the original names have spaces or underscores ('_') separating words (within a name) needing capitalization. 8 Choose the object type in which you want to search (Query, Macro, Form, Report, or Module) and push the appropriate button to start the search. For Form and Report searches you can specify via the check boxes what properties to search (RecordSource & SourceObject, ControlName, ControlSource & LinkFields, RowSource, Report Sorting/Grouping fields, OtherProperties - all other properties that can specify macros or functions), and Caption & ValidationText & StatusText. 9 Assuming you haven't checked the "Replace Without Asking" box, when a match is found a message box is displayed. The object and property in which the match was found is indicated at the top of the box. Just underneath that, the current value of the property is displayed. The line after that shows what the value will be changed to. At the bottom are three buttons: Yes, No, and Cancel. You can select "Yes" to do the replacement, "No" to proceed to the next match, or "Cancel" to stop the entire process. Notes: 1) FIND AND REPLACE CAN BE A DANGEROUS TOOL. IF YOU'RE NOT SURE WHAT YOU'RE DOING, DON'T DO IT. Even if you think you know what you're doing, always have a backup copy of your database that is relatively up to date. 2) For reports, searching the Sorting/Grouping fields slows down the find substantially (up to 10 times slower than without searching sorting/grouping). If you don't need to search in the sorting/grouping fields then uncheck the Report Sorting/Grouping option box. Also, searching report sorting/grouping fields uses SendKeys and control-c (copy) and control-v (paste). If you have redefined these keys (as hot keys for example) it won't work. If it doesn't work you'll have to do report searches without the sorting/grouping option checked. When (if) Microsoft provides access to these properties from Access Basic I will eliminate the reliance on SendKeys (which will also dramatically speed things up). What Find and Replace Searches: Queries: The field specification, table/query names, and criteria. Forms: Properties that can specify macros, functions, field names, control names, or table/query names: BeforeUpdate, AfterUpdate, OnEnter, OnExit, OnDblClick, OnPush, ValidationRule, DefaultValue, RecordSource, ControlName, ControlSource, RowSource, RowSourceType, SourceObject, LinkMasterFields, and LinkChildFields properties. It also will search (if the option is checked) Caption, StatusText, and ValidationText. Reports: Properties that can specify macros, functions, field names, control names, or table/query names: OnOpen, OnClose, OnFormat, OnPrint, RecordSource, SourceObject, ControlName, ControlSource, LinkChildFields, LinkMasterFields, RowSource, and Sorting/Grouping properties. Other properties: Caption. Macros: Macro Labels (Names), Conditions, and Parameters to Macro Actions Modules: The module search simply invokes the Microsoft search function for modules. The limitations and bugs of MSAccess apply here. Version Information: ver 1.00 8/18/93 Initial Version ver 1.01 8/18/93 Added SourceObject and LinkFields properties to Form and Report searches. ver 1.02 8/24/93 Added "Match Whole Word Only" option. Altered Find In Queries to avoid GPF bug in Access when reading a query's SQL string that is longer than 1K. ver 1.03 9/9/93 Added searching of report sorting/grouping fields. Added Module searches. Added the DefaultValue property to form searches. Converted from Access 1.1 format to 1.0 format to be compatible with 1.0 users. Added gas gauge progress indication. ver 1.04 9/20/93 Added a clean-up to proper case option. ver 1.05 10/21/93 Added RowSourceType to properties searched in forms (since you can specify an function here). Added option to search in Caption, ValidationText, and StatusText properties in forms and reports. Added "Replace Without Asking" option. Fixed module search so it will complete properly. Rights, Permissions and Disclaimers: Find and Replace Software, Copyright 1993 Rick Fisher, All Rights Reserved This Program is supplied "AS IS" WITHOUT ANY WARRANTY OF ANY KIND. In no event shall the author be liable for any damages whatsoever in connection with your use of this program. Please note that this software is totally unsupported. All software has bugs and this is no exception (this is not commercial software and thus has not been extensively tested). USE AT YOUR OWN RISK. You may use this software free of charge for your personal use under the following conditions: 1) you agree to the above disclaimer of warranty & liability, 2) you agree not to remove the disclaimer or any of the copyright notices, and 3) you agree not to sell or resell the software (or any part of it). Rick Fisher, CompuServe ID 70743,2514. (Replace.txt 10/21/93)